In [3]:
import pandas.io.data as web
import seaborn as sns
from sqlalchemy import create_engine
import datetime
import pandas as pd
from pygments import highlight
from pygments.lexers.sql import SqlLexer
from pygments.formatters import HtmlFormatter, LatexFormatter
from IPython import display
import functools as ft
import matplotlib.pyplot as plt
import scipy as sp
import scipy.interpolate
from __future__ import division
import numpy as np
import matplotlib.dates as mdates
from scipy.optimize import minimize
from matplotlib.finance import candlestick2_ohlc
from datetime import date
%matplotlib inline
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from matplotlib import cm

CONNECTION_STRING = 'mssql+pymssql://IVYuser:resuyvi@vita.ieor.columbia.edu'

# Gets the database connection
def get_connection():
    engine = create_engine(CONNECTION_STRING)
    return engine.connect()

# Query database and return results in dataframe
def query_dataframe(query, connection=None):
    if connection is None:
        connection = get_connection()
    return pd.read_sql(query, connection)

# Query database using external file and return results in dataframe
def query_dataframe_f(filename, connection=None):
    if connection is None:
        connection = get_connection()
    with open(filename, 'r') as handle:
        return pd.read_sql(handle.read(), connection)

# Print sql query and query results
def print_and_query(filename, connection=None, use_latex=False):
    if connection is None:
        connection = get_connection()
    with open(filename, 'r') as handle:
        sql = handle.read()
        if use_latex:
            display_obj = display.Latex(highlight(
                sql, SqlLexer(), LatexFormatter()))
        else:
            formatter = HtmlFormatter()
            display_obj = display.HTML(
                '<style type="text/css">{}</style>{}'
                .format(
                formatter.get_style_defs('.highlight'),
                highlight(sql, SqlLexer(), formatter)))
        display.display(
            display_obj,
            pd.read_sql(sql, connection)
        ) 

Exercise 3

Recall in the first lecture we discussed the possible impact of a large trade. Having a minute database allows us to examine the consequences of such a trade on a finer scale. Pick a stock from the LiveVol database and find the largest options trade to occur at least one week removed from an Earnings date and at least two times larger than any trade for the subsequent 3 days. We wish to examine minute by minute over the 3-day period.

a) Generate an implied volatility surface for your choice just prior to the large trade. If space is an issue concentrate on a surface centered about the strike and series of the large trade
b) Follow the vol surface minute by minute after the large trade
c) Can you characterize a relaxation time scale for the vol surface
d) Propose a possible trading scheme for a high frequency trader to monetize a future disturbance of this kind

In the LiveVol database we have trade tick data and minute by minute calcualations for Apple, Google and Tesla stocks options in October 2013. We looked at all stocks and analyzed the largest option trades at least one week removed from an earnings date.

In class we saw an interesting example where the whole volatility surface of the stock FDC shifted significantly downwards due to a large option trade. We were hoping that we would be able to find a similar impact from a trade in the tick database but unfortunately we were not able to. That might be due to the fact that all of the stocks we have data for are very liquid so that we would need an extremely large trade to have an obvious and lasting impact on the dynamics of the surface. Since we only have data for one month it might be the case that none of the trades for our stocks in October were large enough to have an impact on price levels or spreads. At least not an impact that is observable for more than a minute.

c)
If we had found a trade that clearly impacted the surface we could have characterized a relaxation time by observing how long it took the surface to revert back to it's "regular" state from before the trade. Since we can't observe a disturbance in the volatilty suraface we can think of two possibilities. If there is a temporary disturbance in the surface due to a large trade it either subdues in less than a minute or the trade needs to be larger relative to average trade size than any of the examples we have observed to appear in the first place.

d)
If one can reliably identify a relaxtion time for the surface of a particular stock and the relaxation time is long enough so that trades can be made before the surface reverts to it's normal state then a simple trading scheme would be take a mean-reverting position. A trader could monitor the options on the stock and whenever he sees a large trade that he expects will disturb the volatility surface he can go short (long) implied volatility on options with the same or similar strike and expiration if he expects the disturbance to be an upwards (downwards) shift. Then close the position after the surface has reverted to it's normal state. Although he would always have to be aware of other events surrounding the options such as earnings, expirations, etc.

Apple

Closest earnings date: 2013-10-28
Analysis Date Range: 2013-1-1 - 2013-10-21

In [4]:
# Get 20 largest trades for Apple
q = '''
-- Get tick data for stock
select symbol, [timestamp], expiration, strike, optiontype, 
tradesize, tradePrice, tradeConditionID, canceledTradeConditionID
into #data_trade
from XFDATA.dbo.lv_options_trades
where symbol = 'AAPL'
and timestamp < '2013-10-21'
and tradeSize > 0

-- Find largest trades
select top 20 *
from #data_trade
order by tradeSize desc

drop table #data_trade
'''

apple_trades  = query_dataframe(q)
apple_trades
Out[4]:
symbol timestamp expiration strike optiontype tradesize tradePrice tradeConditionID canceledTradeConditionID
0 AAPL 2013-10-09 12:32:04.7500000 2013-11-16 530.0 c 5000 5.40 13 0
1 AAPL 2013-10-10 14:23:27.2500000 2013-11-16 500.0 c 4500 14.30 106 0
2 AAPL 2013-10-10 15:26:42.4250000 2013-10-11 500.0 c 3021 0.11 18 0
3 AAPL 2013-10-09 12:31:29.8500000 2013-11-16 505.0 c 2500 11.10 13 0
4 AAPL 2013-10-18 12:24:54.8500000 2013-11-16 525.0 c 2350 9.25 35 1
5 AAPL 2013-10-18 14:12:56.9500000 2013-11-16 525.0 c 2350 9.35 35 0
6 AAPL 2013-10-18 14:17:43.4000000 2013-11-16 525.0 c 2350 9.25 35 2
7 AAPL 2013-10-10 14:23:17.3250000 2013-10-19 450.0 c 2275 39.10 106 0
8 AAPL 2013-10-15 10:33:15.1250000 2013-11-16 445.0 p 2160 2.42 38 0
9 AAPL 2013-10-07 10:40:16.4750000 2013-11-16 415.0 p 2100 1.31 106 0
10 AAPL 2013-10-01 10:03:52.0000000 2013-11-16 445.0 p 2050 7.50 106 0
11 AAPL 2013-10-04 10:09:18.9750000 2013-11-16 525.0 c 2000 5.35 13 0
12 AAPL 2013-10-04 10:05:50.8250000 2013-11-16 425.0 p 2000 3.10 37 0
13 AAPL 2013-10-17 10:50:18.5000000 2013-10-19 505.0 c 1800 1.85 35 0
14 AAPL 2013-10-18 14:12:56.9500000 2013-12-21 520.0 c 1692 16.49 35 0
15 AAPL 2013-10-18 14:17:34.6500000 2013-12-21 520.0 c 1692 16.35 35 2
16 AAPL 2013-10-18 12:24:54.8500000 2013-12-21 520.0 c 1692 16.35 35 1
17 AAPL 2013-10-09 11:00:46.7750000 2013-11-16 420.0 p 1500 2.68 37 0
18 AAPL 2013-10-17 10:01:31.3750000 2013-10-19 500.0 p 1500 1.70 0 1
19 AAPL 2013-10-17 10:01:35.5500000 2013-10-19 500.0 c 1500 4.50 0 1

The largest trade occured at 12:32 on October 9th and was for 5000 call option contracts with expiration 2013-11-16 and strike 530. However, it's not two times larger than any trade for the following three days since the second (4500) and third (3021) largest trades occur the next day. Looking at the three trading days following those trades we can see that they don't fulfil the criteria either. Since there are no trades that match the criteria in the problem description we will just analyze the largest trade of the month.

In [8]:
# Get volatility surface data
q = '''
select symbol, [timestamp], expiration, strike, [open], high, low, [close], 
tradeVolume, bidsize, bestbid, asksize, bestask, impliedUndPrice, activeUndPrice, iv,
datediff(day,convert(date,[timestamp]),expiration) as ExpD
from XFDATA.dbo.lv_minute_options_calcs
where symbol = 'AAPL'
and root = 'AAPL'
and timestamp between '2013-10-09 12:25' and '2013-10-09 12:40'
and optionType = 'c'
and iv > 0.05
and strike between 450 and 550
and datediff(day,convert(date,[timestamp]),expiration) < 100
order by timestamp, strike, expiration
'''

# Display minute-by-minute date around trade (K=530,T=2013-11-16)
apple_large = query_dataframe(q)
mask = (apple_large.strike == 530) & (apple_large.ExpD == 38)
apple_large.loc[mask,:]
Out[8]:
symbol timestamp expiration strike open high low close tradeVolume bidsize bestbid asksize bestask impliedUndPrice activeUndPrice iv ExpD
97 AAPL 2013-10-09 12:25:00 2013-11-16 530.0 5.44 5.44 5.44 5.44 1 108 5.35 6 5.45 478.75 478.75 0.34 38
220 AAPL 2013-10-09 12:26:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 25 5.45 11 5.55 479.05 479.06 0.34 38
344 AAPL 2013-10-09 12:27:00 2013-11-16 530.0 5.55 5.55 5.55 5.55 1 54 5.40 55 5.55 479.25 479.25 0.34 38
467 AAPL 2013-10-09 12:28:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 84 5.45 7 5.55 479.45 479.45 0.34 38
591 AAPL 2013-10-09 12:29:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 34 5.40 14 5.50 479.21 479.21 0.34 38
714 AAPL 2013-10-09 12:30:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 57 5.45 13 5.55 479.35 479.35 0.34 38
837 AAPL 2013-10-09 12:31:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 78 5.45 7 5.55 479.35 479.35 0.34 38
960 AAPL 2013-10-09 12:32:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 32 5.45 12 5.55 479.38 479.38 0.34 38
1083 AAPL 2013-10-09 12:33:00 2013-11-16 530.0 5.40 5.40 5.40 5.40 5000 42 5.45 17 5.55 479.38 479.38 0.34 38
1206 AAPL 2013-10-09 12:34:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 13 5.50 22 5.60 479.38 479.38 0.34 38
1329 AAPL 2013-10-09 12:35:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 33 5.55 12 5.65 479.64 479.66 0.34 38
1452 AAPL 2013-10-09 12:36:00 2013-11-16 530.0 5.64 5.65 5.64 5.65 10 93 5.60 48 5.75 480.03 480.03 0.34 38
1576 AAPL 2013-10-09 12:37:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 45 5.60 29 5.70 480.11 480.11 0.34 38
1699 AAPL 2013-10-09 12:38:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 2 5.60 7 5.65 480.11 480.11 0.34 38
1821 AAPL 2013-10-09 12:39:00 2013-11-16 530.0 5.65 5.65 5.65 5.65 6 41 5.60 6 5.70 480.00 480.00 0.34 38
1944 AAPL 2013-10-09 12:40:00 2013-11-16 530.0 0.00 0.00 0.00 0.00 0 2 5.55 29 5.65 479.95 479.95 0.34 38

Interestingly it seems like the trade had no discernable impact, at least not on it's own series (K=530,T=2013-11-16). There is almost no trading activity before or after the trade, the price is similar and the spread does not change after trade (stays at around 10 cents).
The trade price is lower than the best bid so it looks like someone is selling these contracts and since there is no observable impact we can conclude that this trade was simply not large enough to have an impact on the market. In other words there is enough liquidity in Apple to handle this transaction. However, we must keep in mind that our data is only on a minute-by-minute scale and there might have been a short term impact on the bid ask spread which was resolved in less than a minute, but we can't analyze that using the data we have access to.

We plot the volatility surface centered around the strike and expiration of our option from 12:25 to 12:40, as is expected there is no observable impact from the trade. If there was an impact we should have seen it occuring at 12:33 as shift or twist in the surface, but the surface stays in a rigid structure throughout the whole period.

In [9]:
# Plot minute-by-minute volatility surface
for idx, row in apple_large.groupby('timestamp'):
    # 2D grid construction
    spline = sp.interpolate.Rbf(row.strike,row.ExpD,row.iv,function='thin-plate')
    xi = np.linspace(min(row.strike), max(row.strike))
    yi = np.linspace(min(row.ExpD), max(row.ExpD))
    X, Y = np.meshgrid(xi, yi)
    # 3D interpolation
    Z = spline(X,Y)
    fig = plt.figure(figsize=(20, 8))
    ax = fig.gca(projection='3d')
    surf = ax.plot_surface(X, Y, Z, rstride=1, cstride=1,cmap=plt.cm.coolwarm, linewidth=0.5, antialiased=True)
    fig.colorbar(surf, shrink=0.5, aspect=5)
    ax.set_xlabel('Strike')
    ax.set_ylabel('Time-to-maturity')
    ax.set_zlabel('Implied volatility')
    ax.set_zlim([0,1])
    plt.title(idx)

Google

Closest earnings date: 2013-10-17
Analysis Date Range: 2013-1-1 - 2013-10-10 and 2013-10-24 - 2013-10-31

In [10]:
# Get 20 largest trades for Google
q = '''
-- Get tick data for stock
select symbol, [timestamp], expiration, strike, optiontype, 
tradesize, tradePrice, tradeConditionID, canceledTradeConditionID
into #data_trade
from XFDATA.dbo.lv_options_trades
where symbol = 'GOOG'
and (timestamp < '2013-10-10' or timestamp > '2013-10-24')
and tradeSize > 0

-- Find largest trades
select top 20 *
from #data_trade
order by tradeSize desc

drop table #data_trade
'''

google_trades  = query_dataframe(q)
google_trades
Out[10]:
symbol timestamp expiration strike optiontype tradesize tradePrice tradeConditionID canceledTradeConditionID
0 GOOG 2013-10-25 12:00:12.8750000 2013-10-25 1015.0 p 1482 2.94 35 0
1 GOOG 2013-10-25 12:00:12.8750000 2013-10-25 1025.0 p 1482 11.94 35 0
2 GOOG 2013-10-24 09:51:58.9250000 2013-12-21 1020.0 c 1200 43.80 7 0
3 GOOG 2013-10-30 10:50:33.4500000 2013-11-01 1065.0 c 1132 0.90 35 0
4 GOOG 2013-10-30 10:50:33.4500000 2013-11-01 1075.0 c 1132 0.65 35 0
5 GOOG 2013-10-30 09:54:45.4750000 2013-11-01 1000.0 p 989 0.33 35 0
6 GOOG 2013-10-30 09:54:45.4750000 2013-11-01 1010.0 p 989 0.63 35 0
7 GOOG 2013-10-24 10:00:55.8500000 2013-10-25 1025.0 p 862 2.94 35 0
8 GOOG 2013-10-24 10:00:55.8500000 2013-10-25 1015.0 p 862 1.52 35 0
9 GOOG 2013-10-24 09:57:18.4000000 2013-10-25 1015.0 p 859 1.55 35 0
10 GOOG 2013-10-24 09:57:18.4000000 2013-10-25 1025.0 p 859 3.30 35 0
11 GOOG 2013-10-30 11:01:42.5000000 2013-11-01 1025.0 p 519 2.80 95 0
12 GOOG 2013-10-24 09:51:38.3500000 2013-11-16 1065.0 c 500 12.00 7 0
13 GOOG 2013-10-24 09:52:14.5000000 2013-12-21 1090.0 c 500 16.10 7 0
14 GOOG 2013-10-24 09:52:30.3250000 2013-12-21 1080.0 c 500 18.90 7 0
15 GOOG 2013-10-28 13:15:36.8250000 2014-01-18 1000.0 c 500 51.20 13 0
16 GOOG 2013-10-25 14:46:17.2750000 2013-11-16 1040.0 c 500 9.50 35 0
17 GOOG 2013-10-25 14:46:17.2750000 2013-11-16 1035.0 c 500 11.10 35 0
18 GOOG 2013-10-25 14:46:17.2750000 2013-11-16 1065.0 c 500 4.40 35 0
19 GOOG 2013-10-25 14:46:26.4500000 2013-12-21 1080.0 c 500 9.90 35 0

The largest trade of October is shared between two trades and it looks like they are a part of a spread. The trade occured at 12:00 on October 25th and was for 1482 put option contracts expiring at the end of that day with strikes 1015 and 1025. Since the trade occured at the expiration date of the options we can reasonably assume that in this case someone is closing out a spread position and it's unlikely that the trade had an impact on the market. No other smaller trades in the month fulfil the criteria we are looking for so we will go forward and analyze the activity around this trade.

In [23]:
# Get volatility surface data
q = '''
select symbol, [timestamp], expiration, strike, [open], high, low, [close], 
tradeVolume, bidsize, bestbid, asksize, bestask, impliedUndPrice, activeUndPrice, iv,
datediff(day,convert(date,[timestamp]),expiration) as ExpD
from XFDATA.dbo.lv_minute_options_calcs
where symbol = 'GOOG'
and root = 'GOOG'
and timestamp between '2013-10-25 11:50' and '2013-10-25 12:10'
and optionType = 'p'
and strike between 900 and 1100
and datediff(day,convert(date,[timestamp]),expiration) < 100
and iv > 0.05
order by timestamp, strike, expiration
'''
# Display minute-by-minute date around trade (K=1025,T=2013-10-25)
google_large  = query_dataframe(q)
mask = (google_large.strike == 1015) & (google_large.ExpD == 0)
mask2 = (google_large.strike == 1025) & (google_large.ExpD == 0)
google_large.loc[mask,:].append(google_large.loc[mask2,:])
Out[23]:
symbol timestamp expiration strike open high low close tradeVolume bidsize bestbid asksize bestask impliedUndPrice activeUndPrice iv ExpD
184 GOOG 2013-10-25 11:50:00 2013-10-25 1015.0 1.97 2.05 1.97 2.00 23 1 2.00 3 2.20 1015.47 1015.47 0.17 0
494 GOOG 2013-10-25 11:51:00 2013-10-25 1015.0 2.12 2.71 2.12 2.53 246 7 2.25 9 2.40 1014.77 1014.77 0.17 0
807 GOOG 2013-10-25 11:52:00 2013-10-25 1015.0 2.25 2.50 1.82 2.50 118 13 2.35 10 2.50 1014.57 1014.57 0.17 0
1119 GOOG 2013-10-25 11:53:00 2013-10-25 1015.0 2.35 2.45 2.14 2.20 22 14 2.10 10 2.35 1015.00 1015.00 0.17 0
1441 GOOG 2013-10-25 11:54:00 2013-10-25 1015.0 2.20 2.39 2.10 2.27 32 1 2.05 1 2.30 1015.00 1015.00 0.16 0
1751 GOOG 2013-10-25 11:55:00 2013-10-25 1015.0 2.35 2.45 2.35 2.45 11 4 2.25 1 2.55 1014.77 1014.77 0.18 0
2062 GOOG 2013-10-25 11:56:00 2013-10-25 1015.0 2.44 2.50 2.34 2.50 44 1 2.40 20 2.65 1014.60 1014.60 0.18 0
2373 GOOG 2013-10-25 11:57:00 2013-10-25 1015.0 2.30 2.50 2.25 2.50 36 2 2.15 1 2.40 1014.97 1015.00 0.18 0
2696 GOOG 2013-10-25 11:58:00 2013-10-25 1015.0 2.25 2.45 1.87 2.16 403 1 2.30 1 2.70 1014.55 1014.55 0.17 0
3008 GOOG 2013-10-25 11:59:00 2013-10-25 1015.0 2.11 4.00 2.07 3.30 219 1 3.30 1 3.70 1012.70 1012.73 0.17 0
3323 GOOG 2013-10-25 12:00:00 2013-10-25 1015.0 3.30 3.70 3.30 3.34 50 13 3.00 1 3.40 1013.12 1013.12 0.17 0
3640 GOOG 2013-10-25 12:01:00 2013-10-25 1015.0 3.30 3.30 2.70 2.80 1528 1 2.75 4 3.00 1013.75 1013.75 0.17 0
3958 GOOG 2013-10-25 12:02:00 2013-10-25 1015.0 2.75 2.75 2.70 2.70 7 3 2.60 4 2.85 1014.10 1014.07 0.18 0
4282 GOOG 2013-10-25 12:03:00 2013-10-25 1015.0 2.60 2.70 2.53 2.70 53 2 2.55 2 2.85 1013.97 1013.97 0.17 0
4601 GOOG 2013-10-25 12:04:00 2013-10-25 1015.0 3.00 3.00 2.78 2.78 6 3 2.65 3 3.00 1013.85 1013.85 0.18 0
4918 GOOG 2013-10-25 12:05:00 2013-10-25 1015.0 3.00 3.30 3.00 3.20 20 3 3.40 16 3.80 1012.75 1012.75 0.18 0
5226 GOOG 2013-10-25 12:06:00 2013-10-25 1015.0 3.70 4.70 3.70 4.70 16 3 4.50 6 5.00 1011.45 1011.45 0.17 0
5537 GOOG 2013-10-25 12:07:00 2013-10-25 1015.0 4.45 4.45 3.70 3.81 45 7 3.80 5 4.00 1012.40 1012.40 0.19 0
5857 GOOG 2013-10-25 12:08:00 2013-10-25 1015.0 3.85 4.20 3.77 4.05 37 1 3.80 2 4.20 1012.22 1012.22 0.19 0
6175 GOOG 2013-10-25 12:09:00 2013-10-25 1015.0 4.00 4.00 3.60 3.60 32 25 3.20 10 3.80 1012.85 1012.85 0.18 0
6494 GOOG 2013-10-25 12:10:00 2013-10-25 1015.0 3.70 3.70 3.70 3.70 1 34 3.40 1 3.90 1012.77 1012.77 0.18 0
200 GOOG 2013-10-25 11:50:00 2013-10-25 1025.0 9.74 9.74 9.74 9.74 10 1 9.60 2 9.80 1015.47 1015.47 0.13 0
510 GOOG 2013-10-25 11:51:00 2013-10-25 1025.0 9.80 11.40 9.80 11.40 45 3 10.10 1 10.70 1014.77 1014.77 0.20 0
823 GOOG 2013-10-25 11:52:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 5 10.40 4 11.00 1014.57 1014.57 0.23 0
1135 GOOG 2013-10-25 11:53:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 1 10.00 3 10.60 1015.00 1015.00 0.22 0
1457 GOOG 2013-10-25 11:54:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 3 9.90 1 10.60 1015.00 1015.00 0.18 0
1767 GOOG 2013-10-25 11:55:00 2013-10-25 1025.0 10.50 10.50 10.50 10.50 1 2 10.20 1 10.90 1014.77 1014.77 0.22 0
2078 GOOG 2013-10-25 11:56:00 2013-10-25 1025.0 10.40 10.54 10.40 10.54 11 1 10.40 1 10.90 1014.60 1014.60 0.21 0
2389 GOOG 2013-10-25 11:57:00 2013-10-25 1025.0 10.77 10.77 10.77 10.77 10 2 10.00 1 10.60 1014.97 1015.00 0.23 0
2712 GOOG 2013-10-25 11:58:00 2013-10-25 1025.0 10.90 11.10 10.00 10.86 435 2 10.30 11 11.00 1014.55 1014.55 0.18 0
3024 GOOG 2013-10-25 11:59:00 2013-10-25 1025.0 10.81 12.70 10.77 12.45 206 1 12.10 1 12.80 1012.70 1012.73 0.24 0
3339 GOOG 2013-10-25 12:00:00 2013-10-25 1025.0 12.60 12.60 12.49 12.49 25 12 11.70 1 12.40 1013.12 1013.12 0.22 0
3656 GOOG 2013-10-25 12:01:00 2013-10-25 1025.0 12.04 12.04 11.50 11.70 1487 27 11.20 2 11.90 1013.75 1013.75 0.22 0
3974 GOOG 2013-10-25 12:02:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 1 11.00 2 11.60 1014.10 1014.07 0.26 0
4298 GOOG 2013-10-25 12:03:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 2 10.90 22 11.60 1013.97 1013.97 0.23 0
4617 GOOG 2013-10-25 12:04:00 2013-10-25 1025.0 11.13 11.33 11.13 11.33 2 1 11.10 2 11.70 1013.85 1013.85 0.24 0
4934 GOOG 2013-10-25 12:05:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 1 12.20 4 12.80 1012.75 1012.75 0.26 0
5242 GOOG 2013-10-25 12:06:00 2013-10-25 1025.0 13.50 13.60 13.50 13.60 13 1 13.80 1 14.40 1011.45 1011.45 0.24 0
5553 GOOG 2013-10-25 12:07:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 2 12.60 1 13.20 1012.40 1012.40 0.27 0
5873 GOOG 2013-10-25 12:08:00 2013-10-25 1025.0 12.93 12.93 12.93 12.93 2 1 12.70 101 13.50 1012.22 1012.22 0.26 0
6191 GOOG 2013-10-25 12:09:00 2013-10-25 1025.0 12.50 12.60 11.91 12.60 6 7 11.90 4 12.90 1012.85 1012.85 0.26 0
6510 GOOG 2013-10-25 12:10:00 2013-10-25 1025.0 0.00 0.00 0.00 0.00 0 21 12.10 125 13.10 1012.77 1012.77 0.27 0

The table above show the price and volatility data for both options around the trade (K=1015,T=2013-11-25) and (K=1025,T=2013-11-25). It looks like this trade is a part of a larger trade that is spread out into parts from 11:58 to 12:01 for around 2000 contracts. We can't see the whole order book for the options but since the bid and ask sizes are so low and it's so close to expiration we can assume that the option is not very liquid. Therefore, it is not surprising to see these big trades temporarly affect the option prices (bid/ask spread) since the trades are likely clearing up a big portion of the order book. After the trades we can observe that the option prices are gradually climbing but that is probably due to the fact that the stock price is dropping and since the option is really close to expiration it's price is very sensitive to all underlying price changes. At least it is hard to attribute it to this trade with any confidence and there is no clear disturbance followed by a relaxation. It is more informative to look at the big picture, the whole volatility surface, to determine if the trade had an effect on the dynamics of the stock options that day.

We plot the volatility surface centered around the strike and expiration of our option from 10:50 to 12:10, there does seem to be some action in the shorter maturity 1000 to 1100 strikes but after closer inspection these changes are simply due to missing data. Calculations are missing from the database on all timestamps where the surface is flat on those regions. Example: All data available at 11:53 but K=[1000,1100] has missing data on 11:54. Taking this into account we can't spot any impact obvious impacts on the dynamics of the surface from the trades. The minute-by-minute dynamics are similar to those we observed when looking at it in the three days leading up to the trade.

In [24]:
# Plot minute-by-minute volatility surface
for idx, row in google_large.groupby('timestamp'):
    # 2D grid construction
    spline = sp.interpolate.Rbf(row.strike,row.ExpD,row.iv,function='thin-plate')
    xi = np.linspace(min(row.strike), max(row.strike))
    yi = np.linspace(min(row.ExpD), max(row.ExpD))
    X, Y = np.meshgrid(xi, yi)
    # 3D interpolation
    Z = spline(X,Y)
    fig = plt.figure(figsize=(15, 8))
    ax = fig.gca(projection='3d')
    surf = ax.plot_surface(X, Y, Z, rstride=1, cstride=1,cmap=plt.cm.coolwarm, linewidth=0.5, antialiased=True)
    fig.colorbar(surf, shrink=0.5, aspect=5)
    ax.set_xlabel('Strike')
    ax.set_ylabel('Time-to-maturity')
    ax.set_zlabel('Implied volatility')
    ax.set_zlim([0,2])
    plt.title(idx)
C:\Users\Helgi\Anaconda3\lib\site-packages\matplotlib\pyplot.py:516: RuntimeWarning: More than 20 figures have been opened. Figures created through the pyplot interface (`matplotlib.pyplot.figure`) are retained until explicitly closed and may consume too much memory. (To control this warning, see the rcParam `figure.max_open_warning`).
  max_open_warning, RuntimeWarning)

Tesla

Closest earnings date: 2013-11-5
Analysis Date Range: 2013-1-1 - 2013-10-29

In [16]:
# Get 20 largest trades for Tesla
q = '''
-- Get tick data for stock
select symbol, [timestamp], expiration, strike, optiontype, 
tradesize, tradePrice, tradeConditionID, canceledTradeConditionID
into #data_trade
from XFDATA.dbo.lv_options_trades
where symbol = 'TSLA'
and timestamp < '2013-10-29'
and tradeSize > 0

-- Find largest trades
select top 20 *
from #data_trade
order by tradeSize desc

drop table #data_trade
'''

tesla_trades  = query_dataframe(q)
tesla_trades
Out[16]:
symbol timestamp expiration strike optiontype tradesize tradePrice tradeConditionID canceledTradeConditionID
0 TSLA 2013-10-10 10:25:41.6500000 2013-10-11 185.0 c 2237 0.21 35 0
1 TSLA 2013-10-10 10:25:41.6500000 2013-10-11 180.0 c 2237 0.41 35 0
2 TSLA 2013-10-24 15:42:31.7000000 2015-01-17 170.0 c 2000 44.00 7 0
3 TSLA 2013-10-24 15:42:48.3750000 2015-01-17 190.0 c 2000 36.90 13 0
4 TSLA 2013-10-01 12:42:52.1000000 2015-01-17 180.0 c 1500 51.60 35 0
5 TSLA 2013-10-01 12:43:02.1500000 2015-01-17 210.0 c 1500 39.80 35 0
6 TSLA 2013-10-02 15:59:18.6000000 2013-10-04 195.0 c 1274 0.50 95 0
7 TSLA 2013-10-25 09:30:32.7000000 2013-11-16 340.0 c 1082 0.01 18 0
8 TSLA 2013-10-01 15:58:20.6000000 2014-01-18 180.0 p 1000 18.50 37 0
9 TSLA 2013-10-02 14:59:29.1750000 2013-10-04 205.0 c 1000 0.13 0 0
10 TSLA 2013-10-03 10:39:44.7500000 2013-10-04 150.0 p 952 0.34 35 0
11 TSLA 2013-10-03 10:39:44.7500000 2013-10-04 155.0 p 952 0.46 35 0
12 TSLA 2013-10-11 15:43:18.7500000 2013-10-19 185.0 p 941 8.93 35 0
13 TSLA 2013-10-11 15:43:18.7500000 2013-10-11 185.0 p 941 6.73 35 0
14 TSLA 2013-10-03 10:32:36.2000000 2013-10-04 185.0 c 902 0.30 35 0
15 TSLA 2013-10-03 10:32:36.2000000 2013-10-04 190.0 c 902 0.18 35 0
16 TSLA 2013-10-03 10:25:43.9000000 2015-01-17 50.0 p 900 2.85 106 0
17 TSLA 2013-10-23 14:05:20.8250000 2013-11-16 190.0 c 900 5.30 35 0
18 TSLA 2013-10-23 14:05:20.8250000 2013-11-16 190.0 c 900 5.30 35 0
19 TSLA 2013-10-23 14:05:20.9750000 2013-11-16 175.0 c 900 9.50 35 0

It looks like we have the same case as for Google for the largest trade, it is shared between two trades and it looks like it is someone closing out a spread trade. It occured on October 10th at 10:25 and was for 2237 call option contracts at strikes 180 and 185. In addition, it seems like the 6 largest trades in October are all spread trades. We don't expect these spread trades to have a noticable impact on the market or surface dynamics but since no other smaller trades in the month fulfil the criteria we are looking for so we will go forward and analyze the activity around this trade.

In [27]:
# Get volatility surface data
q = '''
select symbol, [timestamp], expiration, strike, [open], high, low, [close], 
tradeVolume, bidsize, bestbid, asksize, bestask, impliedUndPrice, activeUndPrice, iv,
datediff(day,convert(date,[timestamp]),expiration) as ExpD
from XFDATA.dbo.lv_minute_options_calcs
where symbol = 'TSLA'
and timestamp between '2013-10-10 10:20' and '2013-10-10 10:35'
and optionType = 'c'
and strike between 150 and 250
and datediff(day,convert(date,[timestamp]),expiration) < 100
and iv > 0.05
order by timestamp, strike, expiration
'''
# Display minute-by-minute date around trade (K=185,T=2013-10-11)
tesla_large  = query_dataframe(q)
tesla_large.head()
mask = (tesla_large.strike == 180) & (tesla_large.ExpD == 1)
mask2 = (tesla_large.strike == 185) & (tesla_large.ExpD == 1)
tesla_large.loc[mask,:].append(tesla_large.loc[mask2,:])
Out[27]:
symbol timestamp expiration strike open high low close tradeVolume bidsize bestbid asksize bestask impliedUndPrice activeUndPrice iv ExpD
39 TSLA 2013-10-10 10:20:00 2013-10-11 180.0 0.00 0.00 0.00 0.00 0 10 0.37 15 0.40 171.08 171.08 0.65 1
170 TSLA 2013-10-10 10:21:00 2013-10-11 180.0 0.35 0.37 0.35 0.36 8 33 0.33 37 0.40 170.90 170.93 0.65 1
301 TSLA 2013-10-10 10:22:00 2013-10-11 180.0 0.35 0.35 0.35 0.35 3 47 0.33 37 0.40 170.96 170.96 0.64 1
431 TSLA 2013-10-10 10:23:00 2013-10-11 180.0 0.33 0.39 0.33 0.39 8 1 0.32 41 0.40 170.90 170.93 0.65 1
561 TSLA 2013-10-10 10:24:00 2013-10-11 180.0 0.40 0.40 0.40 0.40 1 52 0.37 15 0.40 171.36 171.36 0.62 1
692 TSLA 2013-10-10 10:25:00 2013-10-11 180.0 0.40 0.46 0.38 0.46 149 1 0.40 110 0.48 171.56 171.56 0.65 1
823 TSLA 2013-10-10 10:26:00 2013-10-11 180.0 0.38 0.46 0.38 0.41 2371 1 0.39 67 0.46 171.62 171.62 0.64 1
953 TSLA 2013-10-10 10:27:00 2013-10-11 180.0 0.45 0.47 0.45 0.47 6 1 0.45 1 0.49 171.99 171.99 0.64 1
1083 TSLA 2013-10-10 10:28:00 2013-10-11 180.0 0.45 0.45 0.45 0.45 5 60 0.41 101 0.49 172.10 172.10 0.62 1
1213 TSLA 2013-10-10 10:29:00 2013-10-11 180.0 0.49 0.50 0.48 0.50 107 2 0.46 157 0.56 172.48 172.51 0.63 1
1343 TSLA 2013-10-10 10:30:00 2013-10-11 180.0 0.00 0.00 0.00 0.00 0 2 0.42 63 0.56 172.52 172.52 0.61 1
1474 TSLA 2013-10-10 10:31:00 2013-10-11 180.0 0.54 0.54 0.53 0.53 52 1 0.47 155 0.56 172.61 172.61 0.62 1
1604 TSLA 2013-10-10 10:32:00 2013-10-11 180.0 0.51 0.52 0.51 0.52 2 25 0.50 95 0.55 172.55 172.54 0.63 1
1735 TSLA 2013-10-10 10:33:00 2013-10-11 180.0 0.50 0.50 0.45 0.45 56 32 0.42 196 0.49 172.23 172.20 0.62 1
1866 TSLA 2013-10-10 10:34:00 2013-10-11 180.0 0.00 0.00 0.00 0.00 0 1 0.40 21 0.45 172.15 172.15 0.61 1
1996 TSLA 2013-10-10 10:35:00 2013-10-11 180.0 0.42 0.42 0.34 0.35 21 38 0.34 38 0.43 171.93 171.93 0.60 1
46 TSLA 2013-10-10 10:20:00 2013-10-11 185.0 0.13 0.13 0.12 0.12 30 47 0.12 30 0.17 171.08 171.08 0.71 1
177 TSLA 2013-10-10 10:21:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 20 0.12 51 0.17 170.90 170.93 0.72 1
308 TSLA 2013-10-10 10:22:00 2013-10-11 185.0 0.12 0.12 0.12 0.12 20 10 0.12 54 0.17 170.96 170.96 0.72 1
438 TSLA 2013-10-10 10:23:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 33 0.12 30 0.16 170.90 170.93 0.72 1
568 TSLA 2013-10-10 10:24:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 33 0.12 23 0.16 171.36 171.36 0.70 1
699 TSLA 2013-10-10 10:25:00 2013-10-11 185.0 0.15 0.18 0.15 0.16 143 43 0.12 12 0.16 171.56 171.56 0.69 1
830 TSLA 2013-10-10 10:26:00 2013-10-11 185.0 0.18 0.21 0.16 0.21 2382 109 0.12 6 0.18 171.62 171.62 0.70 1
960 TSLA 2013-10-10 10:27:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 91 0.13 6 0.18 171.99 171.99 0.69 1
1090 TSLA 2013-10-10 10:28:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 155 0.13 6 0.18 172.10 172.10 0.68 1
1220 TSLA 2013-10-10 10:29:00 2013-10-11 185.0 0.18 0.18 0.18 0.18 6 113 0.12 49 0.21 172.48 172.51 0.67 1
1350 TSLA 2013-10-10 10:30:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 81 0.12 40 0.21 172.52 172.52 0.67 1
1481 TSLA 2013-10-10 10:31:00 2013-10-11 185.0 0.15 0.15 0.15 0.15 18 65 0.13 51 0.21 172.61 172.61 0.67 1
1611 TSLA 2013-10-10 10:32:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 86 0.13 78 0.21 172.55 172.54 0.67 1
1742 TSLA 2013-10-10 10:33:00 2013-10-11 185.0 0.13 0.15 0.13 0.14 147 43 0.12 62 0.17 172.23 172.20 0.67 1
1873 TSLA 2013-10-10 10:34:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 11 0.12 5 0.15 172.15 172.15 0.66 1
2003 TSLA 2013-10-10 10:35:00 2013-10-11 185.0 0.00 0.00 0.00 0.00 0 10 0.12 193 0.15 171.93 171.93 0.67 1

The table above show the price and volatility data for both options around the trade (K=180,T=2013-10-11) and (K=1025,T=2013-10-11). The analysis for Google pretty much applies here as well, we have a spread of options close to expiry and large trades. The trades do seem to affect the option bid/ask spreads although less here than in the Google case. But there is no clear period of disturbance followed by a relaxation.

We plot the volatility surface centered around the strike and expiration of our option from 10:25 to 10:40. Yet again the volatility surface shows no disturbance around the trade (10:26).

In [21]:
# Plot minute-by-minute volatility surface
for idx, row in tesla_large.groupby('timestamp'):
    # 2D grid construction
    spline = sp.interpolate.Rbf(row.strike,row.ExpD,row.iv,function='thin-plate')
    xi = np.linspace(min(row.strike), max(row.strike))
    yi = np.linspace(min(row.ExpD), max(row.ExpD))
    X, Y = np.meshgrid(xi, yi)
    # 3D interpolation
    Z = spline(X,Y)
    fig = plt.figure(figsize=(15, 8))
    ax = fig.gca(projection='3d')
    surf = ax.plot_surface(X, Y, Z, rstride=1, cstride=1,cmap=plt.cm.coolwarm, linewidth=0.5, antialiased=True)
    fig.colorbar(surf, shrink=0.5, aspect=5)
    ax.set_xlabel('Time-to-maturity')
    ax.set_ylabel('Strike')
    ax.set_zlabel('Implied volatility')
    ax.set_zlim([0,2])
    plt.title(idx)